﻿Option Explicit Off
Option Strict Off

Public Class clsExcel

    Private Const mcstrFileNotFound As String = "File does not exist."

    Private mobjApp As Object
    Private mobjBook As Object
    Private mobjSheet As Object

    'Private mobjBookSrc As Object
    'Private mobjBookDes As Object
    'Private mobjSheetSrc As Object
    'Private mobjSheetDes As Object


    Public ReadOnly Property LastRow() As Integer
        Get
            Dim intLastRow As Integer

            intLastRow = 0

            If mobjSheet Is Nothing Then Return intLastRow

            intLastRow = mobjSheet.UsedRange.Rows.Count + 1

            If intLastRow > 65536 Then intLastRow = 65536

            Return intLastRow

        End Get
    End Property


    Public ReadOnly Property LastCol() As Integer
        Get
            Dim intLastCol As Integer

            intLastCol = 0

            If mobjSheet Is Nothing Then Return intLastCol

            intLastCol = mobjSheet.UsedRange.Columns.Count + 1

            If intLastCol > 65536 Then intLastCol = 65536

            Return intLastCol
        End Get
    End Property


    Public Function fncCreateXlsApp() As Boolean

        fncCreateXlsApp = False

        Try
            'create excell application
            Try
                mobjApp = CreateObject("Excel.Application")
            Catch e As Exception
                Return False
            End Try

            mobjApp.Visible = False

            Return True

        Catch ex As Exception
            Throw ex
        End Try

    End Function


    Public Function fncOpenBook(ByVal strFile As String) As Boolean
        fncOpenBook = False

        Try
            xOpenBook(mobjBook, strFile)

            Return True

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function fncOpenSheet(ByVal strSheetName As String) As Boolean
        fncOpenSheet = False
        Try
            If mobjBook Is Nothing Then Exit Function

            'then open worksheet
            'For Each objSheet In mobjBook.WorkSheets
            '    'find sheet with name
            '    If String.Compare(objSheet.Name, strSheetName) <> 0 Then Continue For
            '    mobjSheet = objSheet
            '    Exit For
            'Next

            mobjSheet = mobjBook.WorkSheets(strSheetName)

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function fncOpenSheet(ByVal intSheetNo As Integer) As Boolean
        fncOpenSheet = False
        Try
            If mobjBook Is Nothing Then Exit Function

            mobjSheet = mobjBook.WorkSheets(intSheetNo)

            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function fncGetCellValue(ByVal intCol As Integer, ByVal intRow As Integer) As Object

        Dim objRange As Object = Nothing
        Dim objCellData As Object = Nothing

        Try
            objRange = mobjSheet.Range(fncCalC2A(intCol) & intRow, fncCalC2A(intCol) & intRow)

            objCellData = objRange.Value

        Catch ex As Exception
            Throw ex
        Finally
            xReleaseObject(objRange)
        End Try

        Return objCellData

    End Function


    Public Function fncGetCellValue(ByVal strCol As String, ByVal intRow As Integer) As Object

        Dim objRange As Object = Nothing
        Dim objCellData As Object = Nothing

        Try
            Dim strCell As String

            strCell = strCol & intRow.ToString

            objRange = mobjSheet.Range(strCell, strCell)

            objCellData = objRange.Value

            'If mobjSheet Is Nothing Then Return objCellData

            'objCellData = mobjSheet.Cells(intCol, intRow).Value

        Catch ex As Exception
            Throw ex
        Finally
            xReleaseObject(objRange)
        End Try

        Return objCellData

    End Function


    Public Function fncSetCellValue(ByVal strCol As String, _
                                    ByVal intRow As Integer, _
                                    ByVal objValue As Object) As Boolean

        Dim objRange As Object = Nothing    'セル範囲
        Dim strCell As String               'セル範囲指定用の文字列

        Try

            'セル範囲の文字列を作成
            strCell = strCol + clsCommon.fncCnvString(intRow)

            'セル範囲を取得
            objRange = mobjSheet.Range(strCell)

            '貼り付け実行
            objRange.Value = objValue

            'mobjSheet.Cells(intCol, intRow) = objValue

            Return True
        Catch ex As Exception
            Throw ex
        Finally
            xReleaseObject(objRange)
        End Try

    End Function


    Public Function fncsetcellvalue(ByVal intcol As Integer, _
                                ByVal introw As Integer, _
                                ByVal objvalue As Object) As Boolean

        Dim objrange As Object = Nothing    'セル範囲
        Dim strcell As String               'セル範囲指定用の文字列

        Try

            'セル範囲の文字列を作成
            strcell = fncCalC2A(intcol) + clsCommon.fncCnvString(introw)

            'セル範囲を取得
            objrange = mobjSheet.range(fncCalC2A(intcol) + clsCommon.fncCnvString(introw))

            '貼り付け実行
            objrange.value = objvalue

            'mobjsheet.cells(intcol, introw) = objvalue

            Return True
        Catch ex As Exception
            Throw ex
        Finally
            xReleaseObject(objrange)
        End Try

    End Function


    Public Function fncDisplay() As Boolean
        fncDisplay = False

        Try
            mobjApp.Visible = True

            Return True

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function fncExit(ByVal blnCloseAll As Boolean) As Boolean
        fncExit = False

        Try
            If blnCloseAll Then
                If mobjBook IsNot Nothing Then mobjBook.Close(False)
                If mobjApp IsNot Nothing Then mobjApp.Quit()
            End If

            xReleaseObject(mobjSheet)
            xReleaseObject(mobjBook)
            xReleaseObject(mobjApp)

            Return True

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function fncCloseBook() As Boolean

        Try
            If mobjBook IsNot Nothing Then mobjBook.Close(False)

            xReleaseObject(mobjSheet)
            xReleaseObject(mobjBook)

            Return True

        Catch ex As Exception
            Throw ex
        Finally

        End Try

    End Function


    Public Function fncCalC2A(ByVal lngCol As Long) As String

        Dim strWork As String = Nothing
        Dim lngWork As Long

        Try

            fncCalC2A = ""

            'A1へ変換
            strWork = ""

            '上の桁
            lngWork = (lngCol - 1) \ 26
            If lngWork >= 1 Then
                strWork = Chr(64 + lngWork)
            End If

            '下の桁
            lngWork = (lngCol - 1) Mod 26
            lngWork = lngWork + 65
            strWork = strWork + Chr(lngWork)

        Catch ex As Exception
            Throw ex
        Finally

        End Try

        Return strWork

    End Function


    Private Function xOpenBook(ByRef objBook As Object, ByVal strFileName As String) As Boolean
        xOpenBook = False
        Dim objFI As System.IO.FileInfo = Nothing

        Try
            objFI = New System.IO.FileInfo(strFileName)

            If Not objFI.Exists Then

                clsCommon.fncShowWarning(mcstrFileNotFound)
                Return False

            End If

            'open excel file
            objBook = mobjApp.Workbooks.Open(strFileName, , False, , , , True)
            'mobjSheet = mobjBook.WorkSheets.Item(intSheetNo)

            Return True

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Private Function xReleaseObject(ByRef obj As Object) As Boolean

        xReleaseObject = False

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing

            Return True

        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Function

End Class
